﻿using Dapper;
using Estsh.Core.Base;
using Estsh.Core.Dapper;
using Estsh.Core.Wms.IRepositories;
using Estsh.Core.Model.Result;
using Estsh.Core.Repositories;
using System.Collections;
using System.Data;
using System.Text;
using System.Security.Cryptography;
using System.Text.Json;
using Estsh.Core.Models;
using Newtonsoft.Json.Linq;
using Estsh.Core.Model.EnumUtil;

namespace Estsh.Core.Wms.Repositories
{
    public class SemiFinishInStockRepository : BaseRepository<BaseEntity>, ISemiFinishInStockRepository
    {
        public SemiFinishInStockRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {

        }

        public List<WmsRack> GetRackNo(string RKNO)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string result = string.Empty;

                StringBuilder sql = new StringBuilder(1024);
                sql.Append("select * from wms_rack (nolock) where rack_no=@rack_no  ");
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@rack_no", RKNO);

                return dbConn.Query<WmsRack>(sql.ToString(), parameters).ToList();
            }
        }

        public List<SysStock> CheckCartonNoStatus(string cartonNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string result = string.Empty;

                StringBuilder sql = new StringBuilder(1024);
                sql.Append("select * from sys_stock as a left join wms_rack_part_package as b on a.part_no=b.rack_part where carton_no=@carton_no  ");
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@carton_no", cartonNo);

                return dbConn.Query<SysStock>(sql.ToString(), parameters).ToList();
            }
        }

        public List<SysStock> GetSemiFinishList(string carton_no, string partno, string num)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string result = string.Empty;

                StringBuilder sql = new StringBuilder(1024);
                sql.Append(@" select top " + num + " * from sys_stock where locate_id =  (select dest_locate_id from sys_pdline where pdline_code = ( select pdline_code from mes_out_pdline                                where serial_number = @carton_no )) and part_no = @partno and enabled = 'Y'  ");
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@carton_no", carton_no);
                parameters.Add("@partno", partno.Trim());

                return dbConn.Query<SysStock>(sql.ToString(), parameters).ToList();
            }
        }

        public List<SysLocate> CheckLocateStatus(string locateName)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string result = string.Empty;

                StringBuilder sql = new StringBuilder(1024);
                sql.Append(@"SELECT a.*,B.*,c.erp_warehouse FROM dbo.sys_locate a (nolock) 
                            LEFT JOIN sys_locate_part b (nolock) ON a.locate_id = b.locate_id 
                            LEFT join sys_zone as c (nolock) on a.zone_id=c.zone_id  
                            WHERE a.locate_name =@locateName ");
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@locateName", locateName);

                return dbConn.Query<SysLocate>(sql.ToString(), parameters).ToList();
            }
        }

        public string Insertinfo(string locateName, string RKno, string productNo, string Num, string loginId)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();

                List<SysStock> oldStock = CheckCartonNoStatus(productNo);
                List<SysLocate> locate = CheckLocateStatus(locateName);
                List<SysStock> RKInfo = CheckCartonNoStatus(RKno);
                List<SysStock> Getsemi = GetSemiFinishList(productNo, oldStock[0].PartNo, Num);

                List<string> sqlStrings = new List<string>();
                List<DynamicParameters> parameterList = new List<DynamicParameters>();


                //新增料架与包装信息
                StringBuilder updateString2 = new StringBuilder(1024);
                updateString2.Append(@"INSERT INTO [dbo].[wms_rack_package]
                                         ([rack_no]
                                         ,[corton_no]
                                         ,[factory_id]
                                         ,[factory_code]
                                         ,[enabled]
                                         ,[create_userid]
                                         ,[create_time]
                                         ,[update_userid]
                                         ,[update_time]
                                         ,[guid],rack_num,
                                            locate_id,locate_name,warehouse_id,warehouse_name,zone_id,zone_name,status,part_spec)
                                   VALUES (@rack_no,@corton_no,@factory_id,@factory_code,@enabled,@create_userid,@create_time,@update_userid,@update_time,@guid,@rack_num,
                                    @locate_id,@locate_name,@warehouse_id,@warehouse_name,@zone_id,@zone_name,@status,@part_spec
                                )");
                DynamicParameters updateStringparameters2 = new DynamicParameters();
                updateStringparameters2.Add("@rack_no", RKno);
                updateStringparameters2.Add("@corton_no", oldStock[0].PartNo);
                updateStringparameters2.Add("@factory_id", locate[0].FactoryId);
                updateStringparameters2.Add("@factory_code", locate[0].FactoryCode);
                updateStringparameters2.Add("@enabled", "Y");
                updateStringparameters2.Add("@create_userid", loginId);
                updateStringparameters2.Add("@create_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                updateStringparameters2.Add("@guid", Guid.NewGuid());
                updateStringparameters2.Add("@rack_num", Num);
                updateStringparameters2.Add("@update_userid", loginId);
                updateStringparameters2.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                updateStringparameters2.Add("@locate_id", locate[0].LocateId);
                updateStringparameters2.Add("@locate_name", locate[0].LocateName);
                updateStringparameters2.Add("@warehouse_id", locate[0].WarehouseId);
                updateStringparameters2.Add("@warehouse_name", locate[0].WarehouseName);
                updateStringparameters2.Add("@zone_id", locate[0].ZoneId);
                updateStringparameters2.Add("@zone_name", locate[0].ZoneName);
                updateStringparameters2.Add("@status", (int)WmsEnumUtil.StockStatus.INSTOCKED);
                updateStringparameters2.Add("@part_spec", oldStock[0].PartSpec);


                sqlStrings.Add(updateString2.ToString());
                parameterList.Add(updateStringparameters2);

                //var semiSum = Getsemi.Sum(a => a.Qty);
                //if (semiSum >= Convert.ToInt32(Num))
                //{
                //    StringBuilder updateString = new StringBuilder(1024);
                //    updateString.Append("update sys_stock set qty=@qty,status=@status, update_userid=@update_userid,update_time=@update_time where carton_no=@carton_no ");
                //    DynamicParameters updateStringparameters = new DynamicParameters();
                //    updateStringparameters.Add("@qty", Num);
                //    updateStringparameters.Add("@carton_no", RKno);
                //    updateStringparameters.Add("@status", (int)WmsEnumUtil.StockStatus.INSTOCKED);
                //    updateStringparameters.Add("@update_userid", loginId);
                //    updateStringparameters.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

                //    sqlStrings.Add(updateString.ToString());
                //    parameterList.Add(updateStringparameters);
                //}
                //else
                //{
                //    return "数量不足，目前只有" + Getsemi.Count + "件";
                //}

                if (RKInfo.Count == 0)
                {
                    //在STOCK中插入料架
                    StringBuilder updateString3 = new StringBuilder(1024);
                    updateString3.Append(@" INSERT INTO [dbo].[sys_stock]
                                        ([vendor_id]
                                        ,[vendor_code]
                                        ,[carton_no]
                                        ,[carton_type]
                                        ,[part_id]
                                        ,[part_no]
                                        ,[part_spec]
                                        ,[lot_no]
                                        ,[fix_lot_no]
                                        ,[status]
                                        ,[qty]
                                        ,[snp_qty]
                                        ,[locate_id]
                                        ,[locate_name]
                                        ,[group_no]
                                        ,[erp_warehouse]
                                        ,[date_code]
                                        ,[qms_status]
                                        ,[ref_order_no]
                                        ,[unit]
                                        ,[dock]
                                        ,[warehouse_id]
                                        ,[warehouse_name]
                                        ,[zone_id]
                                        ,[zone_name]
                                        ,[printed]
                                        ,[print_time]
                                        ,[remark]
                                        ,[factory_id]
                                        ,[factory_code]
                                        ,[enabled]
                                        ,[create_userid]
                                        ,[create_time]
                                        ,[update_userid]
                                        ,[update_time]
                                        ,[guid])
                                        VALUES(@vendor_id,@vendor_code,@carton_no,@carton_type,@part_id,@part_no,@part_spec,@lot_no,@fix_lot_no,@status,@qty,@snp_qty
,@locate_id,@locate_name,@group_no,@erp_warehouse,@date_code,@qms_status,@ref_order_no,@unit,@dock,@warehouse_id,@warehouse_name,@zone_id,@zone_name
,@printed,@print_time,@remark,@factory_id,@factory_code,@enabled,@create_userid,@create_time,@update_userid,@update_time,@guid )");

                    DynamicParameters updateStringparameters3 = new DynamicParameters();
                    updateStringparameters3.Add("@vendor_id", oldStock[0].VendorId);
                    updateStringparameters3.Add("@vendor_code", oldStock[0].VendorName);
                    updateStringparameters3.Add("@carton_no", RKno);
                    updateStringparameters3.Add("@carton_type", "1");
                    updateStringparameters3.Add("@part_id", "0");
                    updateStringparameters3.Add("@part_no", RKno);
                    updateStringparameters3.Add("@part_spec", RKno);
                    updateStringparameters3.Add("@lot_no", "99999999");
                    updateStringparameters3.Add("@fix_lot_no", RKno);
                    updateStringparameters3.Add("@status", (int)WmsEnumUtil.StockStatus.INSTOCKED);
                    updateStringparameters3.Add("@qty", Num);
                    updateStringparameters3.Add("@snp_qty", Num);
                    updateStringparameters3.Add("@locate_id", locate[0].LocateId);
                    updateStringparameters3.Add("@locate_name", locate[0].LocateName);
                    updateStringparameters3.Add("@group_no", "");
                    updateStringparameters3.Add("@erp_warehouse", "");
                    updateStringparameters3.Add("@date_code", "");
                    updateStringparameters3.Add("@qms_status", "");
                    updateStringparameters3.Add("@ref_order_no", "台车");
                    updateStringparameters3.Add("@unit", "");
                    updateStringparameters3.Add("@dock", "");
                    updateStringparameters3.Add("@warehouse_id", locate[0].WarehouseId);
                    updateStringparameters3.Add("@warehouse_name", locate[0].WarehouseName);
                    updateStringparameters3.Add("@zone_id", locate[0].ZoneId);
                    updateStringparameters3.Add("@zone_name", locate[0].ZoneName);
                    updateStringparameters3.Add("@printed", "");
                    updateStringparameters3.Add("@print_time", "");
                    updateStringparameters3.Add("@remark", "");
                    updateStringparameters3.Add("@factory_id", locate[0].FactoryId);
                    updateStringparameters3.Add("@factory_code", locate[0].FactoryCode);
                    updateStringparameters3.Add("@enabled", oldStock[0].Enabled);
                    updateStringparameters3.Add("@update_userid", loginId);
                    updateStringparameters3.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                    updateStringparameters3.Add("@create_userid", loginId);
                    updateStringparameters3.Add("@create_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                    updateStringparameters3.Add("@guid", Guid.NewGuid());

                    sqlStrings.Add(updateString3.ToString());
                    parameterList.Add(updateStringparameters3);
                }
                else
                {
                    //更新条码表，数量
                    StringBuilder updateString = new StringBuilder(1024);
                    updateString.Append("update sys_stock set qty=@qty,status=@status, update_userid=@update_userid,update_time=@update_time where carton_no=@carton_no ");
                    DynamicParameters updateStringparameters = new DynamicParameters();
                    updateStringparameters.Add("@qty", Num);
                    updateStringparameters.Add("@carton_no", RKno);
                    updateStringparameters.Add("@status", (int)WmsEnumUtil.StockStatus.INSTOCKED);
                    updateStringparameters.Add("@update_userid", loginId);
                    updateStringparameters.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

                    sqlStrings.Add(updateString.ToString());
                    parameterList.Add(updateStringparameters);
                }

                #region 新增 sys_stock_trans 事务表

                StringBuilder updateString4 = new StringBuilder(1024);
                updateString4.Append(@" INSERT INTO[dbo].[sys_stock_trans]
                                                    ([trans_code]
                                                    ,[carton_no]
                                                    ,[part_id]
                                                    ,[part_no]
                                                    ,[part_spec]
                                                    ,[src_locate_id]
                                                    ,[src_locate_name]
                                                    ,[dest_locate_id]
                                                    ,[dest_locate_name]
                                                    ,[old_qty]
                                                    ,[new_qty]
                                                    ,[trans_qty]
                                                    ,[old_status]
                                                    ,[new_status]
                                                    ,[old_qms_status]
                                                    ,[new_qms_status]
                                                    ,[unit]
                                                    ,[factory_id]
                                                    ,[factory_code]
                                                    ,[src_erp_warehouse]
                                                    ,[dest_erp_warehouse]
                                                    ,[src_warehouse_id]
                                                    ,[src_warehouse_name]
                                                    ,[dest_warehouse_id]
                                                    ,[dest_warehouse_name]
                                                    ,[src_zone_id]
                                                    ,[src_zone_name]
                                                    ,[dest_zone_id]
                                                    ,[dest_zone_name]
                                                    ,[enabled]
                                                    ,[update_userid]
                                                    ,[update_time]
                                                    ,[create_userid]
                                                    ,[create_time]
                                                    ,[guid])
                                                  VALUES (@trans_code,@carton_no,@part_id,@part_no,@part_spec,@src_locate_id ,@src_locate_name ,@dest_locate_id ,@dest_locate_name ,@old_qty 
                                        ,@new_qty ,@trans_qty ,@old_status ,@new_status ,@old_qms_status ,@new_qms_status ,@unit ,@factory_id ,@factory_code ,@src_erp_warehouse ,@dest_erp_warehouse ,@src_warehouse_id ,@src_warehouse_name ,@dest_warehouse_id ,@dest_warehouse_name ,@src_zone_id ,@src_zone_name ,@dest_zone_id ,@dest_zone_name ,@enabled ,@update_userid ,@update_time ,@create_userid ,@create_time ,@guid )");

                DynamicParameters updateStringparameters4 = new DynamicParameters();
                updateStringparameters4.Add("@trans_code", (int)WmsEnumUtil.TransType.IN_STOCK_RACK_PACKAGE);
                updateStringparameters4.Add("@carton_no", RKno);
                updateStringparameters4.Add("@part_id", oldStock[0].PartId);
                updateStringparameters4.Add("@part_no", oldStock[0].PartNo);
                updateStringparameters4.Add("@part_spec", oldStock[0].PartSpec);
                updateStringparameters4.Add("@src_locate_id", oldStock[0].LocateId);
                updateStringparameters4.Add("@src_locate_name", oldStock[0].LocateName);
                updateStringparameters4.Add("@dest_locate_id", locate[0].LocateId);
                updateStringparameters4.Add("@dest_locate_name", locate[0].LocateName);
                updateStringparameters4.Add("@old_qty", oldStock[0].Qty);
                updateStringparameters4.Add("@new_qty", Num);
                updateStringparameters4.Add("@trans_qty", oldStock[0].Qty);
                updateStringparameters4.Add("@old_status", oldStock[0].Status);
                updateStringparameters4.Add("@new_status", (int)WmsEnumUtil.StockStatus.INSTOCKED);
                updateStringparameters4.Add("@old_qms_status", oldStock[0].QmsStatus);
                updateStringparameters4.Add("@new_qms_status", oldStock[0].QmsStatus);
                updateStringparameters4.Add("@unit", oldStock[0].Unit);
                updateStringparameters4.Add("@factory_id", oldStock[0].FactoryId);
                updateStringparameters4.Add("@factory_code", oldStock[0].FactoryCode);
                updateStringparameters4.Add("@src_erp_warehouse", oldStock[0].ErpWarehouse);
                updateStringparameters4.Add("@dest_erp_warehouse", oldStock[0].ErpWarehouse);
                updateStringparameters4.Add("@src_warehouse_id", oldStock[0].WarehouseId);
                updateStringparameters4.Add("@src_warehouse_name", oldStock[0].WarehouseName);
                updateStringparameters4.Add("@dest_warehouse_id", locate[0].WarehouseId);
                updateStringparameters4.Add("@dest_warehouse_name", locate[0].WarehouseName);
                updateStringparameters4.Add("@src_zone_id", oldStock[0].ZoneId);
                updateStringparameters4.Add("@src_zone_name", oldStock[0].ZoneName);
                updateStringparameters4.Add("@dest_zone_id", locate[0].ZoneId);
                updateStringparameters4.Add("@dest_zone_name", locate[0].ZoneName);
                updateStringparameters4.Add("@enabled", oldStock[0].Enabled);
                updateStringparameters4.Add("@update_userid", loginId);
                updateStringparameters4.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                updateStringparameters4.Add("@create_userid", loginId);
                updateStringparameters4.Add("@create_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                updateStringparameters4.Add("@guid", Guid.NewGuid());

                sqlStrings.Add(updateString4.ToString());
                parameterList.Add(updateStringparameters4);

                #endregion

                //执行事务
                IDbTransaction transaction = dbConn.BeginTransaction();

                bool successCount = true;
                for (int i = 0; i < sqlStrings.Count; i++)
                {
                    if (dbConn.Execute(sqlStrings[i], parameterList[i], transaction) < 1)
                    {
                        successCount = false;
                    }
                }
                if (successCount)
                {
                    transaction.Commit();
                    return "OK" + oldStock[0].PartSpec;
                }
                else
                {
                    transaction.Rollback();
                    return "操作失败，请重新尝试！";
                }
            }
        }
    }
}
